package com.wind.trademark.base.dao.impl;

import java.util.Map;

import org.apache.commons.lang.StringUtils;

public class CategorySql {

	public static String queryCategorySQL(Map<String,Object> params ) {

		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT category.*   ,    ");
		stringBuffer.append("	IF(                                                 ");
		stringBuffer.append("		EXISTS(                                         ");
		stringBuffer.append("			SELECT                                      ");
		stringBuffer.append("				sub_category.ID                             ");
		stringBuffer.append("			FROM                                        ");
		stringBuffer.append("				bs_category sub_category                ");
		stringBuffer.append("			WHERE                                       ");
		stringBuffer.append("				category.ID = sub_category.PARENT       ");
		stringBuffer.append("		),                                              ");
		stringBuffer.append("		'true',                                         ");
		stringBuffer.append("		'false'                                         ");
		stringBuffer.append("	) HAS_CHILDREN                                      ");
		stringBuffer.append("  from bs_category category      ");
		stringBuffer.append("    WHERE 1=1   ");

		if(StringUtils.isNotEmpty((String) params.get("name"))){
			stringBuffer.append("AND (category.CN_NAME LIKE :name or category.EN_NAME LIKE:name ) ");	
			params.put("name","%"+params.get("name")+"%");

		}
		if(StringUtils.isNotEmpty((String) params.get("code"))){
			stringBuffer.append("AND category.CODE LIKE:code ");	
			params.put("code","%"+params.get("code")+"%");
		}
		if(StringUtils.isNotEmpty((String) params.get("parent"))){
			stringBuffer.append("AND category.PARENT=:parent ");	
			params.put("parent", Long.valueOf((String)params.get("parent")));
		}
		stringBuffer.append("ORDER BY category.CODE");
		return stringBuffer.toString();
	}

	public static String goolgeL2CategorySQL() {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT bs_category.ID 'key',  bs_category.CODE 'value' ");
		stringBuffer.append("FROM bs_category bs_category ");
		stringBuffer.append("WHERE bs_category.CODE  LIKE:queryParam ");
		stringBuffer.append("AND CHARACTER_LENGTH(bs_category.CODE) = 4 ");
		stringBuffer.append("ORDER BY bs_category.CODE");
		return stringBuffer.toString();
	}

	public static String goolgeL1AndL2Category(Map<String, Object> params) {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT bs_category.ID 'key',  bs_category.CODE 'value' ");
		stringBuffer.append("FROM bs_category bs_category WHERE 1=1  ");
		if(params.entrySet().size()==1&&StringUtils.isNotEmpty((String) params.get("key"))){
			stringBuffer.append("AND bs_category.ID=:key   ");
		}
		if(StringUtils.isNotEmpty((String) params.get("queryParam"))){
			stringBuffer.append(" AND bs_category.CODE LIKE :queryParam  ");
			stringBuffer.append(" AND CHARACTER_LENGTH(bs_category.CODE) <= 4 ");
			params.put("queryParam", "%"+(String) params.get("queryParam")+"%");
		}
		stringBuffer.append("ORDER BY bs_category.CODE");
		return stringBuffer.toString();
	}

	public static String getL1AndL2Category() {

		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT bs_category.* ");
		stringBuffer.append("FROM bs_category bs_category ");
		stringBuffer.append("WHERE 1=1 ");
		stringBuffer.append("AND CHARACTER_LENGTH(bs_category.CODE) <= 4 ");
		stringBuffer.append("ORDER BY bs_category.CODE");
		return stringBuffer.toString();
	
	}
	public static String getL2Category() {

		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT bs_category.ID 'key', CONCAT(bs_category.CODE,'/',COALESCE(bs_category.CN_NAME, ''),'/',COALESCE(bs_category.CN_NAME, ''))'value' ");
		stringBuffer.append("FROM bs_category bs_category ");
		stringBuffer.append("WHERE 1=1 ");
		stringBuffer.append("AND CHARACTER_LENGTH(bs_category.CODE) = 4 ");
		stringBuffer.append("ORDER BY bs_category.CODE");
		return stringBuffer.toString();
	
	}

	
	public static String deleteCategories() {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("DELETE ");
		stringBuffer.append("FROM bs_category  ");
		stringBuffer.append("WHERE id IN (:ids) ");
		return stringBuffer.toString();
	}

}
